#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table test.dim_shops_his
select shopid,
shopname,
cityid,
cityname,
regionid,
regionname,
create_date,
dt as start_date,
'9999-12-31' as end_date
from test.shops where dt = '$do_date'
union all
select dsh.shopid,
dsh.shopname,
dsh.cityid,
dsh.cityname,
dsh.regionid,
dsh.regionname,
dsh.create_date,
dsh.start_date,
case when dsh.end_date='9999-12-31' and s.shopid is not null 
then date_add('$do_date',-1)
else dsh.end_date
end as end_date
from test.dim_shops_his dsh 
left join (
select * from test.shops where dt = '$do_date') s
on dsh.shopid = s.shopid;
"
hive -e "$sql"
